﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConcordWinManager
{
    public class MSExcelReportGenerator
    {
        private string abc = "ABCDEFGHIJ";

        Excel.Application xl;
        object template = Excel.XlWBATemplate.xlWBATWorksheet;

        /// <summary>
        /// Горизонтальное выравнивание заголовка отчета
        /// </summary>
        object hAlignmentHeaderTop = Excel.Constants.xlCenter;

        /// <summary>
        /// Вертикальное выравнивание заголовка отчета
        /// </summary>
        object vAlignmentHeaderTop = Excel.Constants.xlCenter;

        /// <summary>
        /// Горизонтальное выравнивание заголовка таблицы
        /// </summary>
        object hAlignmentHeaderTable = Excel.Constants.xlCenter;

        /// <summary>
        /// Вертикальное выравнивание заголовка таблицы
        /// </summary>
        object vAlignmentHeaderTable = Excel.Constants.xlTop;

        /// <summary>
        /// Горизонтальное выравнивание тела таблицы
        /// </summary>
        object hAlignmentBody = Excel.Constants.xlGeneral;

        /// <summary>
        /// Вертикальное выравнивание тела таблицы
        /// </summary>
        object vAlignmentBody = Excel.Constants.xlTop;


        /// <summary>
        /// Тру
        /// </summary>
        object bTrue = true;


        public MSExcelReportGenerator()
        {
            if (xl == null)
            {
                xl = new Excel.Application();
                xl.Visible = false;
            }
        }

        /// <summary>
        /// Отчет по заключенным договорам за период
        /// </summary>
        /// <param name="DateBegin"></param>
        /// <param name="DateEnd"></param>
        /// <param name="deleted">Удаленные?</param>
        public void GenerateReportContract(DateTime DateBegin, DateTime DateEnd, bool deleted)
        {
            string begin = string.Format("{0}-{1}-{2}",
                    DateBegin.Year,
                    DateBegin.Month.ToString("0#"),
                    DateBegin.Day.ToString("0#"));

            string end = string.Format("{0}-{1}-{2}",
                    DateEnd.Year,
                    DateEnd.Month.ToString("0#"),
                    DateEnd.Day.ToString("0#"));

            DataSet ds = new DataSet();
            string query = string.Format("EXEC dbo.ReturnContractInfoBetweenDate0290 '{0}', '{1}', '{2}'", begin, end, deleted);
            DB.query_table(query, "dbo.ReturnContractInfoBetweenDate0290", ref ds);

            xl.ScreenUpdating = false;

            object missing = Type.Missing;

            object index = 1;
            object CountList = 1;

            if (ds.Tables.Count > 0)
            {
                xl.Workbooks.Add(template);

                foreach (DataTable dtable in ds.Tables)
                {
                    //xl.SheetsInNewWorkbook = 2; 

                    Excel.Workbook ThisWorkbook = xl.ActiveWorkbook;
                    xl.Worksheets.Add(missing, missing, CountList, missing);
                    Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.ActiveSheet;

                    Excel.Range rng;


                    rng = ws.get_Range("B2", "I2");
                    rng.Merge(missing);
                    rng.Value2 = "Отчет по заключенным договорам";
                    rng.Font.Size = 14;

                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;


                    rng = ws.get_Range("B3", "I3");
                    rng.Merge(missing);
                    rng.Value2 = string.Format("c {0} по {1}", DateBegin.Date.ToShortDateString(), DateEnd.Date.ToShortDateString());
                    rng.Font.Size = 14;
                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;

                    int fontSize = 10;
                    rng = ws.get_Range("A6", missing);
                    rng.Value2 = "      ";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("B6", missing);
                    rng.Value2 = "Контрагент";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("C6", missing);
                    rng.Value2 = "Номер договора";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("D6", missing);
                    rng.Value2 = "Дата заключения"; ;
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("E6", missing);
                    rng.Value2 = "Сумма, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("F6", missing);
                    rng.Value2 = "Аванс, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("G6", missing);
                    rng.Value2 = "Оплачено, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("H6", missing);
                    rng.Value2 = "Остаток, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("I6", missing);
                    rng.Value2 = "Консультант";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    if (deleted)
                    {
                        rng = ws.get_Range("J6", missing);
                        rng.Value2 = "Причина удаления";
                        rng.Font.Size = fontSize;
                        rng.HorizontalAlignment = hAlignmentHeaderTable;
                        rng.VerticalAlignment = vAlignmentHeaderTable;
                    }

                    
                    rng = ws.get_Range("A7", missing);

                    int i = 1;
                    bool bShowInReport;
                    bool bDeleted;

                    int rowoffset = -1;

                    foreach (DataRow dr in dtable.Rows)
                    {
                        bShowInReport = true;
                        bDeleted = false;

                        for (int j = 0; j < dr.Table.Columns.Count; j++)
                        {
                            if (dr[j] is Boolean)
                            {
                                if (dr.Table.Columns[j].Caption == "Deleted")
                                {
                                    bDeleted = Convert.ToBoolean(dr[j].ToString());
                                }
                                else if (dr.Table.Columns[j].Caption == "ShowInReport")
                                {
                                    bShowInReport = Convert.ToBoolean(dr[j].ToString());
                                    if (!bShowInReport)
                                    {
                                        break;
                                    }
                                }
                            }
                            else if (dr[j] is DateTime)
                            {
                                DateTime dt;
                                if (DateTime.TryParse(dr[j].ToString(), out dt))
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = string.Format("{0}.{1}.{2}",
                                        dt.Day.ToString("0#"),
                                        dt.Month.ToString("0#"),
                                        dt.Year
                                        );
                                }
                                else
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                            }
                            else if (dr.Table.Columns[j].Caption == "Консультант")
                            {
                                string[] s = dr[j].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                                rng.get_Offset(i, j + rowoffset).Value2 = string.Format("{0} {1}.{2}.", s[0], s[1][0], s[2][0]);
                            }
                            else if (dr.Table.Columns[j].Caption == "Номер договора")
                            {
                                if (bDeleted)
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString() + " = удален";
                                }
                                else
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                            }
                            else
                            {
                                rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                            }

                            rng.HorizontalAlignment = hAlignmentBody;
                            rng.VerticalAlignment = vAlignmentBody;
                            rng.WrapText = bTrue;
                            //rng.AutoFit();
                        }


                        if (i < dtable.Rows.Count)
                        {
                            rng.get_Offset(i, 0).Value2 = i.ToString();
                        }
                        i++;
                    }

                    //Фикс бага с неотображением последнего номера в столбце нумерации строк.
                    if (i > 1)
                    {
                        rng.get_Offset(i - 1, 0).Value2 = (i - 1).ToString();
                    }


                    rng.get_Offset(i + 2, 3).Value2 = "Итого, руб.";
                    //сумма договора
                    rng.get_Offset(i + 2, 4).Formula = string.Format("=СУММ(E{0}:E{1})", 8, i + 8 - 2);
                    //аванс
                    rng.get_Offset(i + 2, 5).Formula = string.Format("=СУММ(F{0}:F{1})", 8, i + 8 - 2);
                    //оплачено
                    rng.get_Offset(i + 2, 6).Formula = string.Format("=СУММ(G{0}:G{1})", 8, i + 8 - 2);
                    //остаток
                    rng.get_Offset(i + 2, 7).Formula = string.Format("=СУММ(H{0}:H{1})", 8, i + 8 - 2);


                    ws.Cells.Select();
                    ws.Cells.Columns.AutoFit();

                    rng = ws.get_Range("A1", missing);
                    rng.Select();

                    ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                }
            }
            else
            {

            }

            xl.ScreenUpdating = true;
            xl.Visible = true;
        }


        /// <summary>
        /// Отчет по произведенным оплатам
        /// </summary>
        /// <param name="DateBegin">С даты</param>
        /// <param name="DateEnd">По дату</param>
        /// <param name="deleted">Удаленные?</param>
        public void GenerateReportByOplata(DateTime DateBegin, DateTime DateEnd, bool deleted)
        {
            string begin = string.Format("{0}-{1}-{2}",
                    DateBegin.Year,
                    DateBegin.Month.ToString("0#"),
                    DateBegin.Day.ToString("0#"));

            string end = string.Format("{0}-{1}-{2}",
                    DateEnd.Year,
                    DateEnd.Month.ToString("0#"),
                    DateEnd.Day.ToString("0#"));

            DataSet ds = new DataSet();
            string query = string.Format("EXEC dbo.ReturnOplatBetweenDate0_2_11_0 '{0}', '{1}', '{2}'", begin, end, deleted);
            DB.query_table(query, "dbo.ReturnOplatBetweenDate0_2_11_0", ref ds);

            xl.ScreenUpdating = false;

            object missing = Type.Missing;

            object index = 1;
            object CountList = 1;

            if (ds.Tables.Count > 0)
            {
                xl.Workbooks.Add(template);

                foreach (DataTable dtable in ds.Tables)
                {
                    //xl.SheetsInNewWorkbook = 2; 

                    Excel.Workbook ThisWorkbook = xl.ActiveWorkbook;
                    xl.Worksheets.Add(missing, missing, CountList, missing);
                    Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.ActiveSheet;

                    Excel.Range rng;


                    rng = ws.get_Range("B2", "F2");
                    rng.Merge(missing);
                    rng.Value2 = "Отчет по произведенным оплатам";
                    rng.Font.Size = 14;

                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;


                    rng = ws.get_Range("B3", "F3");
                    rng.Merge(missing);
                    rng.Value2 = string.Format("c {0} по {1}", DateBegin.Date.ToShortDateString(), DateEnd.Date.ToShortDateString());
                    rng.Font.Size = 14;
                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;

                    int fontSize = 10;
                    rng = ws.get_Range("A6", missing);
                    rng.Value2 = " ";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("B6", missing);
                    rng.Value2 = "Контрагент";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("C6", missing);
                    rng.Value2 = "Дата оплаты";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("D6", missing);
                    rng.Value2 = "Номер оплаты";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("E6", missing);
                    rng.Value2 = "Основание оплаты"; ;
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("F6", missing);
                    rng.Value2 = "Сумма, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("G6", missing);
                    rng.Value2 = "Консультант";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    if (deleted)
                    {
                        rng = ws.get_Range("G6", missing);
                        rng.Value2 = "Причина удаления";
                        rng.Font.Size = fontSize;
                        rng.HorizontalAlignment = hAlignmentHeaderTable;
                        rng.VerticalAlignment = vAlignmentHeaderTable;
                    }

                    rng = ws.get_Range("A7", missing);

                    int i = 1;
                    foreach (DataRow dr in dtable.Rows)
                    {
                        rng.get_Offset(i, 0).Value2 = i.ToString();
                        for (int j = 0; j < dr.Table.Columns.Count; j++)
                        {
                            if (dr[j] is DateTime)
                            {
                                DateTime dt;
                                if (DateTime.TryParse(dr[j].ToString(), out dt))
                                {
                                    rng.get_Offset(i, j + 1).Value2 = string.Format("{0}.{1}.{2}",
                                        dt.Day.ToString("0#"),
                                        dt.Month.ToString("0#"),
                                        dt.Year
                                        );
                                }
                                else
                                {
                                    rng.get_Offset(i, j + 1).Value2 = dr[j].ToString();
                                }
                            }
                            else if (dr.Table.Columns[j].Caption == "Консультант")
                            {
                                string[] s = dr[j].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                                rng.get_Offset(i, j + 1).Value2 = string.Format("{0} {1}.{2}.", s[0], s[1][0], s[2][0]);
                            }
                            else
                            {
                                rng.get_Offset(i, j + 1).Value2 = dr[j].ToString();
                            }

                            rng.HorizontalAlignment = hAlignmentBody;
                            rng.VerticalAlignment = vAlignmentBody;
                            rng.WrapText = bTrue;
                        }

                        i++;
                    }

                    //Фикс бага с неотображением последнего номера в столбце нумерации строк.
                    if (i > 1)
                    {
                        rng.get_Offset(i - 1, 0).Value2 = (i - 1).ToString();
                    }

                    rng.get_Offset(i + 2, 4).Value2 = "Итого, руб.";
                    rng.get_Offset(i + 2, 5).Formula = string.Format("=СУММ(F{0}:F{1})", 8, i + 8 - 2);




                    ws.Cells.Select();
                    ws.Cells.Columns.AutoFit();

                    rng = ws.get_Range("A1", missing);
                    rng.Select();

                    ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                }
            }
            else
            {

            }

            xl.ScreenUpdating = true;
            xl.Visible = true;
        }

        /// <summary>
        /// Отчет по предоставленным рассрочкам за период
        /// </summary>
        /// <param name="DateBegin"></param>
        /// <param name="DateEnd"></param>
        /// <param name="deleted">Удаленные?</param>
        public void GenerateReportRassrochka(DateTime DateBegin, DateTime DateEnd, bool deleted)
        {
            string begin = string.Format("{0}-{1}-{2}",
                   DateBegin.Year,
                   DateBegin.Month.ToString("0#"),
                   DateBegin.Day.ToString("0#"));

            string end = string.Format("{0}-{1}-{2}",
                    DateEnd.Year,
                    DateEnd.Month.ToString("0#"),
                    DateEnd.Day.ToString("0#"));

            DataSet ds = new DataSet();
            string query = string.Format("EXEC dbo.ReportAllContractRassrochkaBetweenDate0_2_11_0 '{0}', '{1}', '{2}'", begin, end, deleted);
            DB.query_table(query, "dbo.ReportAllContractRassrochkaBetweenDate0_2_11_0", ref ds);

            xl.ScreenUpdating = false;

            object missing = Type.Missing;

            object index = 1;
            object CountList = 1;

            int step = 0;

            Excel.Range rng;

            Excel.Workbook ThisWorkbook;
            Excel.Worksheet ws;

            if (ds.Tables.Count > 0)
            {
                xl.Workbooks.Add(template);

                foreach (DataTable dtable in ds.Tables)
                {
                    //xl.SheetsInNewWorkbook = 2; 

                    step++;


                    ThisWorkbook = xl.ActiveWorkbook;
                    xl.Worksheets.Add(missing, missing, CountList, missing);
                    ws = (Excel.Worksheet)ThisWorkbook.ActiveSheet;

                    string nameStep = "";
                    switch (step)
                    {
                        case 1:
                            nameStep = "Предоставленные рассрочки";
                            break;                       
                        default:
                            nameStep = "Итого";
                            ws.Name = nameStep;

                            rng = ws.get_Range("B2", "I2");
                            rng.Merge(missing);
                            rng.Value2 = "Отчет по предоставленным рассрочкам";
                            rng.Font.Size = 14;
                            rng.HorizontalAlignment = hAlignmentHeaderTop;
                            rng.VerticalAlignment = vAlignmentHeaderTop;

                            rng = ws.get_Range("B3", "I3");
                            rng.Merge(missing);
                            rng.Value2 = "(Итого)";
                            rng.Font.Size = 14;
                            rng.HorizontalAlignment = hAlignmentHeaderTop;
                            rng.VerticalAlignment = vAlignmentHeaderTop;

                            rng = ws.get_Range("B4", "I4");
                            rng.Merge(missing);
                            rng.Value2 = string.Format("c {0} по {1}", DateBegin.Date.ToShortDateString(), DateEnd.Date.ToShortDateString());
                            rng.Font.Size = 14;
                            rng.HorizontalAlignment = hAlignmentHeaderTop;
                            rng.VerticalAlignment = vAlignmentHeaderTop;

                            int k = 0;

                            rng = ws.get_Range("A7", missing);

                            foreach (DataColumn dc in dtable.Columns)
                            {
                                rng.get_Offset(k, 1).Value2 = dc.Caption;
                                k++;
                            }

                            foreach (DataRow dr in dtable.Rows)
                            {
                                k = 0;
                                for (int j = 0; j < dr.Table.Columns.Count; j++)
                                {
                                    rng.get_Offset(k, 2).Value2 = dr[j].ToString();
                                    rng.HorizontalAlignment = Excel.Constants.xlCenter;
                                    k++;
                                }
                            }

                            ws.Cells.Select();
                            ws.Cells.Columns.AutoFit();

                            rng = ws.get_Range("A1", missing);
                            rng.Select();

                            ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;

                            goto itogo;
                            break;
                    }

                    ws.Name = nameStep;


                    rng = ws.get_Range("B2", "I2");
                    rng.Merge(missing);
                    rng.Value2 = "Отчет по предоставленным рассрочкам";
                    rng.Font.Size = 14;


                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;

                    #region .столбцы.
                    rng = ws.get_Range("B3", "I3");
                    rng.Merge(missing);
                    rng.Value2 = string.Format("c {0} по {1}", DateBegin.Date.ToShortDateString(), DateEnd.Date.ToShortDateString());
                    rng.Font.Size = 14;
                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;

                    int fontSize = 10;
                    rng = ws.get_Range("A6", missing);
                    rng.Value2 = " ";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("B6", missing);
                    rng.Value2 = "Контрагент";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("C6", missing);
                    rng.Value2 = "Номер договора";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("D6", missing);
                    rng.Value2 = "Тип рассрочки"; ;
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("E6", missing);
                    rng.Value2 = "Сумма, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("F6", missing);
                    rng.Value2 = "Аванс, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("G6", missing);
                    rng.Value2 = "Оплачено, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("H6", missing);
                    rng.Value2 = "Остаток, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("I6", missing);
                    rng.Value2 = "Оплачено раз";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("J6", missing);
                    rng.Value2 = "Консультант";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    if (deleted)
                    {
                        rng = ws.get_Range("K6", missing);
                        rng.Value2 = "Причина удаления";
                        rng.Font.Size = fontSize;
                        rng.HorizontalAlignment = hAlignmentHeaderTable;
                        rng.VerticalAlignment = vAlignmentHeaderTable;
                    }
                    #endregion
                    rng = ws.get_Range("A7", missing);

                    int i = 1;
                    bool bShowInReport;
                    bool bDeleted;

                    int rowoffset = -1;

                    foreach (DataRow dr in dtable.Rows)
                    {
                        bShowInReport = true;
                        bDeleted = false;

                        for (int j = 0; j < dr.Table.Columns.Count; j++)
                        {
                            if (dr[j] is Boolean)
                            {
                                if (dr.Table.Columns[j].Caption == "Deleted")
                                {
                                    bDeleted = Convert.ToBoolean(dr[j].ToString());
                                }
                                else if (dr.Table.Columns[j].Caption == "ShowInReport")
                                {
                                    bShowInReport = Convert.ToBoolean(dr[j].ToString());
                                    if (!bShowInReport)
                                    {
                                        break;
                                    }
                                }
                            }
                            else if (dr[j] is DateTime)
                            {
                                DateTime dt;
                                if (DateTime.TryParse(dr[j].ToString(), out dt))
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = string.Format("{0}.{1}.{2}",
                                        dt.Day.ToString("0#"),
                                        dt.Month.ToString("0#"),
                                        dt.Year
                                        );
                                }
                                else
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                            }
                            else if (dr.Table.Columns[j].Caption == "Консультант")
                            {
                                string[] s = dr[j].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                                rng.get_Offset(i, j + rowoffset).Value2 = string.Format("{0} {1}.{2}.", s[0], s[1][0], s[2][0]);
                            }
                            else if (dr.Table.Columns[j].Caption == "Номер договора")
                            {
                                if (bDeleted)
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString() + " = удален";
                                }
                                else
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                            }
                            else
                            {
                                //if (rowoffset > 0)
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                                //else
                                //{
                                //    rng.get_Offset(i, j ).Value2 = dr[j].ToString();
                                //}
                            }

                            rng.HorizontalAlignment = hAlignmentBody;
                            rng.VerticalAlignment = vAlignmentBody;
                            rng.WrapText = bTrue;
                        }


                        if (i < dtable.Rows.Count)
                        {
                            rng.get_Offset(i, 0).Value2 = i.ToString();
                        }
                        i++;
                    }

                    //Фикс бага с неотображением последнего номера в столбце нумерации строк.
                    if (i > 1)
                    {
                        rng.get_Offset(i - 1, 0).Value2 = (i - 1).ToString();
                    }


                    rng.get_Offset(i + 2, 3).Value2 = "Итого, руб.";
                    //сумма договора
                    rng.get_Offset(i + 2, 4).Formula = string.Format("=СУММ(E{0}:E{1})", 8, i + 8 - 2);
                    //аванс
                    rng.get_Offset(i + 2, 5).Formula = string.Format("=СУММ(F{0}:F{1})", 8, i + 8 - 2);
                    //оплачено
                    rng.get_Offset(i + 2, 6).Formula = string.Format("=СУММ(G{0}:G{1})", 8, i + 8 - 2);
                    //остаток
                    rng.get_Offset(i + 2, 7).Formula = string.Format("=СУММ(H{0}:H{1})", 8, i + 8 - 2);


                    ws.Cells.Select();
                    ws.Cells.Columns.AutoFit();

                    rng = ws.get_Range("A1", missing);
                    rng.Select();

                    ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                }
            }
            else
            {

            }

        itogo: 

            xl.ScreenUpdating = true;
            xl.Visible = true;
        }

        /// <summary>
        /// Отчет по неоплаченным рассрочкам за период
        /// </summary>
        /// <param name="DateBegin"></param>
        /// <param name="DateEnd"></param>
        /// <param name="deleted">Удаленные?</param>
        public void GenerateReportNeoplatRassrochka2(DateTime DateBegin, DateTime DateEnd, bool deleted)
        {
            string begin = string.Format("{0}-{1}-{2}",
                   DateBegin.Year,
                   DateBegin.Month.ToString("0#"),
                   DateBegin.Day.ToString("0#"));

            string end = string.Format("{0}-{1}-{2}",
                    DateEnd.Year,
                    DateEnd.Month.ToString("0#"),
                    DateEnd.Day.ToString("0#"));

            DataSet ds = new DataSet();
            string query = string.Format("EXEC dbo.ReportNeoplatRassrochkaBetweenDate0_2_17_0 '{0}', '{1}', '{2}'", begin, end, deleted);
            DB.query_table(query, "dbo.ReportNeoplatRassrochkaBetweenDate0_2_17_0", ref ds);

            xl.ScreenUpdating = false;

            object missing = Type.Missing;

            object index = 1;
            object CountList = 1;

            int step = 0;

            Excel.Range rng;

            Excel.Workbook ThisWorkbook;
            Excel.Worksheet ws;

            if (ds.Tables.Count > 0)
            {
                xl.Workbooks.Add(template);

                foreach (DataTable dtable in ds.Tables)
                {
                    //xl.SheetsInNewWorkbook = 2; 

                    step++;
                    

                    ThisWorkbook = xl.ActiveWorkbook;
                    xl.Worksheets.Add(missing, missing, CountList, missing);
                    ws = (Excel.Worksheet)ThisWorkbook.ActiveSheet;

                    

                    string nameStep = "";
                    switch (step)
                    {
                        case 1:
                            nameStep = "Непросроченные рассрочки";
                            break;
                        case 2:                            
                            nameStep = "Просроченные рассрочки";
                            break;
                        default:
                            nameStep = "Итого";                            
                            ws.Name = nameStep;

                            rng = ws.get_Range("B2", "I2");
                            rng.Merge(missing);
                            rng.Value2 = "Отчет по неоплаченным рассрочкам";
                            rng.Font.Size = 14;
                            rng.HorizontalAlignment = hAlignmentHeaderTop;
                            rng.VerticalAlignment = vAlignmentHeaderTop;

                            rng = ws.get_Range("B3", "I3");
                            rng.Merge(missing);
                            rng.Value2 = "(Итого)";
                            rng.Font.Size = 14;
                            rng.HorizontalAlignment = hAlignmentHeaderTop;
                            rng.VerticalAlignment = vAlignmentHeaderTop;

                            rng = ws.get_Range("B4", "I4");
                            rng.Merge(missing);
                            rng.Value2 = string.Format("c {0} по {1}", DateBegin.Date.ToShortDateString(), DateEnd.Date.ToShortDateString());
                            rng.Font.Size = 14;
                            rng.HorizontalAlignment = hAlignmentHeaderTop;
                            rng.VerticalAlignment = vAlignmentHeaderTop;

                            int k = 0;

                            rng = ws.get_Range("A7", missing);

                            foreach (DataColumn dc in dtable.Columns)
                            {
                                rng.get_Offset(k, 1).Value2 = dc.Caption;
                                k++;
                            }

                            foreach (DataRow dr in dtable.Rows)
                            {
                                k = 0;
                                for (int j = 0; j < dr.Table.Columns.Count; j++)
                                {
                                    rng.get_Offset(k, 2).Value2 = dr[j].ToString();
                                    rng.HorizontalAlignment = Excel.Constants.xlCenter;
                                    k++;
                                }
                            }

                            ws.Cells.Select();
                            ws.Cells.Columns.AutoFit();

                            rng = ws.get_Range("A1", missing);
                            rng.Select();

                            ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;

                            goto itogo;
                            break;
                    }

                    ws.Name = nameStep;

                    rng = ws.get_Range("B2", "I2");
                    rng.Merge(missing);
                    rng.Value2 = "Отчет по неоплаченным рассрочкам";
                    rng.Font.Size = 14;
                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;

                    rng = ws.get_Range("B3", "I3");
                    rng.Merge(missing);
                    rng.Value2 = "(" + nameStep + ")";
                    rng.Font.Size = 14;
                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;

                    rng = ws.get_Range("B4", "I4");
                    rng.Merge(missing);
                    rng.Value2 = string.Format("c {0} по {1}", DateBegin.Date.ToShortDateString(), DateEnd.Date.ToShortDateString());
                    rng.Font.Size = 14;
                    rng.HorizontalAlignment = hAlignmentHeaderTop;
                    rng.VerticalAlignment = vAlignmentHeaderTop;


                    #region .столбцы.

                    int a = 6;
                    int fontSize = 10;
                    rng = ws.get_Range("A" + a, missing);
                    rng.Value2 = " ";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("B" + a, missing);
                    rng.Value2 = "Контрагент";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("C" + a, missing);
                    rng.Value2 = "Номер договора";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("D" + a, missing);
                    rng.Value2 = "Тип рассрочки"; ;
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("E" + a, missing);
                    rng.Value2 = "Сумма, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("F" + a, missing);
                    rng.Value2 = "Аванс, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("G" + a, missing);
                    rng.Value2 = "Оплачено, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("H" + a, missing);
                    rng.Value2 = "По плану, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("I" + a, missing);
                    rng.Value2 = "Остаток, руб.";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("J" + a, missing);
                    rng.Value2 = "Оплачено раз";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("K" + a, missing);
                    rng.Value2 = "Срок оплаты";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("L" + a, missing);
                    rng.Value2 = "Последняя оплата";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("M" + a, missing);
                    rng.Value2 = "Просрочено дней";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("N" + a, missing);
                    rng.Value2 = "Консультант";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    if (deleted)
                    {
                        rng = ws.get_Range("O" + a, missing);
                        rng.Value2 = "Причина удаления";
                        rng.Font.Size = fontSize;
                        rng.HorizontalAlignment = hAlignmentHeaderTable;
                        rng.VerticalAlignment = vAlignmentHeaderTable;
                    }

                    #endregion

                    rng = ws.get_Range("A7" , missing);

                    int i = 1;
                    bool bShowInReport;
                    bool bDeleted;

                    int rowoffset = -1;

                    foreach (DataRow dr in dtable.Rows)
                    {
                        bShowInReport = true;
                        bDeleted = false;

                        for (int j = 0; j < dr.Table.Columns.Count; j++)
                        {
                            if (dr[j] is Boolean)
                            {
                                if (dr.Table.Columns[j].Caption == "Deleted")
                                {
                                    bDeleted = Convert.ToBoolean(dr[j].ToString());
                                }
                                else if (dr.Table.Columns[j].Caption == "ShowInReport")
                                {
                                    bShowInReport = Convert.ToBoolean(dr[j].ToString());
                                    if (!bShowInReport)
                                    {
                                        break;
                                    }
                                }
                            }
                            else if (dr[j] is DateTime)
                            {
                                DateTime dt;
                                if (DateTime.TryParse(dr[j].ToString(), out dt))
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = string.Format("{0}.{1}.{2}",
                                        dt.Day.ToString("0#"),
                                        dt.Month.ToString("0#"),
                                        dt.Year
                                        );
                                }
                                else
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                            }
                            else if (dr.Table.Columns[j].Caption == "Консультант")
                            {
                                string[] s = dr[j].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                                rng.get_Offset(i, j + rowoffset).Value2 = string.Format("{0} {1}.{2}.", s[0], s[1][0], s[2][0]);
                            }
                            else if (dr.Table.Columns[j].Caption == "Номер договора")
                            {
                                if (bDeleted)
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString() + " = удален";
                                }
                                else
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                            }
                            else
                            {
                                //if (rowoffset > 0)
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                                //else
                                //{
                                //    rng.get_Offset(i, j ).Value2 = dr[j].ToString();
                                //}
                            }

                            rng.HorizontalAlignment = hAlignmentBody;
                            rng.VerticalAlignment = vAlignmentBody;
                            rng.WrapText = bTrue;
                        }


                        if (i < dtable.Rows.Count)
                        {
                            rng.get_Offset(i, 0).Value2 = i.ToString();
                        }
                        i++;
                    }

                    //Фикс бага с неотображением последнего номера в столбце нумерации строк.
                    if (i > 1)
                    {
                        rng.get_Offset(i - 1, 0).Value2 = (i - 1).ToString();
                    }


                    rng.get_Offset(i + 2, 3).Value2 = "Итого, руб.";
                    //сумма договора
                    rng.get_Offset(i + 2, 4).Formula = string.Format("=СУММ(E{0}:E{1})", 8, i + 8 - 2);
                    //аванс
                    rng.get_Offset(i + 2, 5).Formula = string.Format("=СУММ(F{0}:F{1})", 8, i + 8 - 2);
                    //оплачено
                    rng.get_Offset(i + 2, 6).Formula = string.Format("=СУММ(G{0}:G{1})", 8, i + 8 - 2);
                    //по плану
                    rng.get_Offset(i + 2, 7).Formula = string.Format("=СУММ(H{0}:H{1})", 8, i + 8 - 2);
                    //остаток
                    rng.get_Offset(i + 2, 8).Formula = string.Format("=СУММ(I{0}:I{1})", 8, i + 8 - 2);


                    ws.Cells.Select();
                    ws.Cells.Columns.AutoFit();

                    rng = ws.get_Range("A1", missing);
                    rng.Select();

                    ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                }
            }
            else
            {

            }

        itogo:
            ;



            xl.ScreenUpdating = true;
            xl.Visible = true;
        }


        /// <summary>
        /// Экспорт контрагентов в MS Excel
        /// </summary>
        /// <param name="deleted">Удаленные?</param>
        public void ExportContragent(bool deleted)
        {
            DataSet ds = new DataSet();
            string query = string.Format("EXEC dbo.ExportContragent_0_2_12_0 '{0}'", deleted);
            DB.query_table(query, "dbo.ExportContragent_0_2_12_0", ref ds);

            xl.ScreenUpdating = false;

            object missing = Type.Missing;

            object index = 1;
            object CountList = 1;

            if (ds.Tables.Count > 0)
            {
                xl.Workbooks.Add(template);

                foreach (DataTable dtable in ds.Tables)
                {
                    //xl.SheetsInNewWorkbook = 2; 

                    Excel.Workbook ThisWorkbook = xl.ActiveWorkbook;
                    xl.Worksheets.Add(missing, missing, CountList, missing);
                    Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.ActiveSheet;

                    Excel.Range rng;

                    int a = 1;

                    ws.Name = "Контрагенты";

                    int fontSize = 10;
                    rng = ws.get_Range("A" + a, missing);
                    rng.Value2 = "      ";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("B" + a, missing);
                    rng.Value2 = "Фамилия";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("C" + a, missing);
                    rng.Value2 = "Имя";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("D" + a, missing);
                    rng.Value2 = "Отчество"; ;
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("E" + a, missing);
                    rng.Value2 = "Пол";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("F" + a, missing);
                    rng.Value2 = "Дата рождения";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("G" + a, missing);
                    rng.Value2 = "М/телефон";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("H" + a, missing);
                    rng.Value2 = "E-mail";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("I" + a, missing);
                    rng.Value2 = "Индекс";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("J" + a, missing);
                    rng.Value2 = "Область";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("K" + a, missing);
                    rng.Value2 = "Город";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("L" + a, missing);
                    rng.Value2 = "Улица";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;


                    rng = ws.get_Range("M" + a, missing);
                    rng.Value2 = "Дом";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("N" + a, missing);
                    rng.Value2 = "Корпус";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    rng = ws.get_Range("O" + a, missing);
                    rng.Value2 = "Квартира";
                    rng.Font.Size = fontSize;
                    rng.HorizontalAlignment = hAlignmentHeaderTable;
                    rng.VerticalAlignment = vAlignmentHeaderTable;

                    if (deleted)
                    {
                        rng = ws.get_Range("P" + a, missing);
                        rng.Value2 = "Причина удаления";
                        rng.Font.Size = fontSize;
                        rng.HorizontalAlignment = hAlignmentHeaderTable;
                        rng.VerticalAlignment = vAlignmentHeaderTable;
                    }


                    rng = ws.get_Range("A" + a, missing);

                    int i = 1;
                    bool bShowInReport;
                    bool bDeleted;

                    int rowoffset = -1;

                    foreach (DataRow dr in dtable.Rows)
                    {
                        bShowInReport = true;
                        bDeleted = false;

                        for (int j = 0; j < dr.Table.Columns.Count; j++)
                        {
                            if (dr[j] is Boolean)
                            {
                                if (dr.Table.Columns[j].Caption == "Deleted")
                                {
                                    bDeleted = Convert.ToBoolean(dr[j].ToString());
                                }
                                else if (dr.Table.Columns[j].Caption == "ShowInReport")
                                {
                                    bShowInReport = Convert.ToBoolean(dr[j].ToString());
                                    if (!bShowInReport)
                                    {
                                        break;
                                    }
                                }
                                else if (dr.Table.Columns[j].Caption == "Пол")
                                {
                                    if (Convert.ToBoolean(dr[j].ToString()))
                                    {
                                        rng.get_Offset(i, j + rowoffset).Value2 = "муж.";
                                    }
                                    else
                                    {
                                        rng.get_Offset(i, j + rowoffset).Value2 = "жен.";
                                    }
                                }
                            }
                            else if (dr[j] is DateTime)
                            {
                                DateTime dt;
                                if (DateTime.TryParse(dr[j].ToString(), out dt))
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = string.Format("{0}.{1}.{2}",
                                        dt.Day.ToString("0#"),
                                        dt.Month.ToString("0#"),
                                        dt.Year
                                        );
                                }
                                else
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                            }
                            else if (dr.Table.Columns[j].Caption == "Консультант")
                            {
                                string[] s = dr[j].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                                rng.get_Offset(i, j + rowoffset).Value2 = string.Format("{0} {1}.{2}.", s[0], s[1][0], s[2][0]);
                            }
                            else if (dr.Table.Columns[j].Caption == "Номер договора")
                            {
                                if (bDeleted)
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString() + " = удален";
                                }
                                else
                                {
                                    rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                                }
                            }
                            else
                            {
                                rng.get_Offset(i, j + rowoffset).Value2 = dr[j].ToString();
                            }

                            rng.HorizontalAlignment = hAlignmentBody;
                            rng.VerticalAlignment = vAlignmentBody;
                            rng.WrapText = bTrue;
                            //rng.AutoFit();
                        }


                        if (i < dtable.Rows.Count)
                        {
                            rng.get_Offset(i, 0).Value2 = i.ToString();
                        }
                        i++;
                    }

                    //Фикс бага с неотображением последнего номера в столбце нумерации строк.
                    if (i > 1)
                    {
                        rng.get_Offset(i - 1, 0).Value2 = (i - 1).ToString();
                    }


                    //rng.get_Offset(i + 2, 3).Value2 = "Итого, руб.";
                    ////сумма договора
                    //rng.get_Offset(i + 2, 4).Formula = string.Format("=СУММ(E{0}:E{1})", 8, i + 8 - 2);
                    ////аванс
                    //rng.get_Offset(i + 2, 5).Formula = string.Format("=СУММ(F{0}:F{1})", 8, i + 8 - 2);
                    ////оплачено
                    //rng.get_Offset(i + 2, 6).Formula = string.Format("=СУММ(G{0}:G{1})", 8, i + 8 - 2);
                    ////остаток
                    //rng.get_Offset(i + 2, 7).Formula = string.Format("=СУММ(H{0}:H{1})", 8, i + 8 - 2);


                    ws.Cells.Select();
                    ws.Cells.Columns.AutoFit();

                    rng = ws.get_Range("A1", missing);
                    rng.Select();

                    ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                }
            }
            else
            {

            }

        
            



            xl.ScreenUpdating = true;
            xl.Visible = true;
        }
    }
}